import pymysql
import sqlite3

'''
创建者：付颖斌。创建时间：2020-08-21。
修改时间：2021-04-27
作用：控制和操作数据库，主要是增、删、更新、查询
数据库参数定义方法：(可定义多个数据库)
dataBaseSql = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',    # 数据库引擎
        'TYPE':'mysql',#数据库类型
        'NAME': 'rfidtagbind', # 数据库名称
        'HOST': 'localhost', # 数据库地址，本机 ip 地址 127.0.0.1
        'PORT': 3306, # 端口
        'USER': 'root',  # 数据库用户名
        'PASSWORD': 'dragon', # 数据库密码
        'CHARSET':'UTF8', #字节集
    },
    'testSqlite': {
        'ENGINE': 'django.db.backends.sqlite3',    # 数据库引擎
        'TYPE':'sqlite3',#数据库类型
        'NAME': BASE_DIR / 'db.sqlite3', # 数据库名称

        'USER': 'root',  # 数据库用户名,这个可以不要
        'PASSWORD': 'dragon', # 数据库密码,这个可以不要
        'CHARSET':'UTF8', #字节集 UTF8
    }
}
'''

'''
sqlSelectData()函数详情：
作用：查询数据
tableName：表名，colName：列名，whereConstraint：约束条件，dataBasePara：数据库参数，其值为dataBaseSql['default']，即dataBasePara=dataBaseSql['default']
例：colName=['name','phoneid','shunxu']
testData=sqlSelectData('test',colName,'id=1',dataBaseSql['default'])
'''
def sqlSelectData(tableName, colName, whereConstraint, dataBasePara):
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    if dataBasePara['TYPE']=='mysql':#如果数据库类型是'mysql'，使用mysql连接数据库
        resultData=sqlSelectDataMysql(tableName, colName, whereConstraint, dataBasePara)
    elif dataBasePara['TYPE']=='sqlite3':
        resultData = sqlSelectDataSqlite(tableName, colName, whereConstraint, dataBasePara)
    else:
        resultData='等待补充'
    return resultData  # 本函数返回值为记录数量

'''
sqlUpdateData()函数详情：
作用：更新数据
tableName：表名，colName：列名，updateData：更新的数据矩阵，whereConstraint：约束条件，dataBasePara：数据库参数，其值为dataBaseSql['default']，即dataBasePara=dataBaseSql['default']
更新数据必须有约束值，如果没有，数据库会不执行更新操作，避免因为操作失误将所有数据更新。
例：colName=['name','phoneid','shunxu']
updateData=['王红军','15589883456','9']
whereConstraint='id=1'
testData=sqlUpdateDataLine('test', colName, updateData, whereConstraint, dataBaseSql['default'])
返回值：-1：输入参数错误，一般是列名称不正确。返回值:-2,没有写约束
'''
def sqlUpdateDataLine(tableName, colName, updateData, whereConstraint, dataBasePara):
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    if whereConstraint!='':
        if dataBasePara['TYPE']=='mysql':#如果数据库类型是'mysql'，使用mysql连接数据库
            resultData=sqlUpdateDataLineMysql(tableName, colName, updateData, whereConstraint, dataBasePara)
        elif dataBasePara['TYPE'] == 'sqlite3':
            resultData=sqlUpdateDataLineSqlite(tableName, colName, updateData, whereConstraint, dataBasePara)
        else:
            resultData=0
    else:
        resultData=-2
    return resultData # 本函数返回值为记录数量

'''
sqlInsertDataLine()函数详情：
作用：插入一行数据
tableName：表名，colName：列名，insertData：插入的数据矩阵，dataBasePara：数据库参数，其值为dataBaseSql['default']，即dataBasePara=dataBaseSql['default']
例：colName=['name','phoneid','shunxu']
insertData=['张亮','198834562','3']
testData=sqlInsertDataLine('test', colName, insertData,  dataBaseSql['default'])
返回值：-1：输入参数错误，一般是列名称不正确，有时候是列数量和插入数据的数量不想等
'''
def sqlInsertDataLine(tableName, colName, insertData, dataBasePara):
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    if dataBasePara['TYPE']=='mysql':#如果数据库类型是'mysql'，使用mysql连接数据库
        resultData=sqlInsertDataLineMysql(tableName, colName, insertData, dataBasePara)
    elif dataBasePara['TYPE']=='sqlite3':
        resultData = sqlInsertDataLineSqlite(tableName, colName, insertData, dataBasePara)
    else:
        resultData=0
    return resultData

'''
sqlInsertDataMultLine()函数详情：
作用：插入多行数据
tableName：表名，colName：列名，insertData：插入的数据矩阵，dataBasePara：数据库参数，其值为dataBaseSql['default']，即dataBasePara=dataBaseSql['default']
例：colName=['name','phoneid','shunxu']
insertData=[['张亮','19883456水电费2','5'],
            ['李文','192','6'],
            ['刘林','19886水电费6','7'],
            ]
testData=sqlInsertDataMultLine('test', colName, insertData,  dataBaseSql['default'])
返回值：-1：输入参数错误，一般是列名称不正确，有时候是列数量和插入数据的数量不想等
'''
def sqlInsertDataMultLine(tableName, colName, insertData, dataBasePara):
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    if dataBasePara['TYPE']=='mysql':#如果数据库类型是'mysql'，使用mysql连接数据库
        resultData=sqlInsertDataMultLineMysql(tableName, colName, insertData, dataBasePara)
    elif dataBasePara['TYPE']=='sqlite3':
        resultData = sqlInsertDataMultLineSqlite(tableName, colName, insertData, dataBasePara)
    else:
        resultData=0
    return resultData

'''
sqlDeleteData()函数详情：
作用：删除数据
tableName：表名，whereConstraint：约束条件，dataBasePara：数据库参数，其值为dataBaseSql['default']，即dataBasePara=dataBaseSql['default']
删除数据必须有约束值，如果没有，数据库会不执行删除操作，避免因为操作失误将所有数据删除。
例：whereConstraint="name='张亮'"
testData=sqlDeleteData('test',whereConstraint, dataBaseSql['default'])
返回值：-1：输入参数错误，一般是表名称不正确，或者约束不正确。返回值:-2,没有写约束
'''
def sqlDeleteData(tableName, whereConstraint, dataBasePara):
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    if whereConstraint!='':
        if dataBasePara['TYPE']=='mysql':#如果数据库类型是'mysql'，使用mysql连接数据库
            resultData=sqlDeleteDataMysql(tableName, whereConstraint, dataBasePara)
        elif dataBasePara['TYPE'] == 'sqlite3':
            resultData = sqlDeleteDataSqlite(tableName, whereConstraint, dataBasePara)
        else:
            resultData=0
    else:
        resultData=-2
    return resultData # 本函数返回值为记录数量


'''
一些基础函数的方法********************************************************
'''
'''
colNameMatrxiToString(colName)，将矩阵格式的colName转化为数据库可以识别的string数据
colName=['name','phoneid','shunxu']
返回值为：'name, phoneid, shunxu'
'''
def colNameMatrxiToString(colName):
    resultData=''
    for i in range(len(colName)):
        if i<len(colName)-1:
            resultData=resultData+colName[i]+', '
        else:
            resultData=resultData+colName[i]
    return resultData

def dataMatrxiToString(colName):
    resultData=''
    for i in range(len(colName)):
        if i<len(colName)-1:
            if colName[i]==None:
                colName[i]=''
            resultData=resultData+"'"+colName[i]+"', "
        else:
            resultData=resultData+"'"+colName[i]+"' "
    return resultData
'''
stringToColNameMatrix(colString)，将string数据转换为以','为分割字符的矩阵格式（清除了空格）
colString='name, phoneid, shunxu'
返回值为：['name','phoneid','shunxu']
'''
def stringToColNameMatrix(colString):
    tempStr=colString.replace(" ","")
    resultData=tempStr.split(',')
    return resultData

'''
下面是mysql数据库连接查询数据、更新数据等方法********************************************************
'''
def sqlSelectDataMysql(tableName, colName, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    if whereConstraint!='':
        sqlStr = "select " + colNameString + " from " + tableName + " where " + whereConstraint
    else:
        sqlStr = "select " + colNameString + " from " + tableName
    #print(sqlStr)
    try:
        cur.execute(sqlStr)
        resultData = cur.fetchall() # 获取满足上述条件的记录
        print("sql执行成功，已获取选中数据！")
    except:
        print("查询数据未成功，请检查数据库参数！")
        resultData='fail'
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回取回的数据

def sqlUpdateDataLineMysql(tableName, colName, updateData, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    if(len(colName)==len(updateData)):
        colAndValue=''
        for i in range(len(colName)-1):
            colAndValue=colAndValue+colName[i]+"='"+updateData[i]+"', "
        colAndValue=colAndValue+colName[len(colName)-1]+"='"+updateData[len(updateData)-1]+"' "
        sql = "update " + tableName + " set " + colAndValue + " where " + whereConstraint
        try:
            resultData=cur.execute(sql)
            print("sql执行成功,改变选中数据行数：",resultData)
        except:
            resultData=-1
            print("sql执行失败,请检查输入参数")
    else:
        print('字段数量和保持字符串的数量不想等，请检查！')
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为更新成功记录的数量

def sqlInsertDataLineMysql(tableName, colName, insertData, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    dataString=dataMatrxiToString(insertData)
    sqlStr = 'insert into ' + tableName + ' ('+colNameString+') values(' + dataString +')' # 注意SQL语句必须是str文本
    try:
        resultData=cur.execute(sqlStr)
        print("sql执行成功,插入数据行数：",resultData)
    except:
        resultData=-1
        print("sql执行失败,请检查输入参数")
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为插入成功记录的数量

def sqlInsertDataMultLineMysql(tableName, colName, insertData, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    resultData=0
    for i in range(len(insertData)):
        dataString=dataMatrxiToString(insertData[i])
        sqlStr = 'insert into ' + tableName + ' ('+colNameString+') values(' + dataString +')' # 注意SQL语句必须是str文本
        try:
            tempData=cur.execute(sqlStr)
            resultData=resultData+tempData
        except:
            resultData=-1
    # 下三行是关闭数据库连接
    if resultData==-1:
        print("sql执行失败,请检查输入参数")
    else:
        print("sql插入成功,插入数据行数：",resultData)
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为插入成功记录的数量

def sqlDeleteDataMysql(tableName, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    sql = "delete from " + tableName + " where " + whereConstraint
    try:
        resultData=cur.execute(sql)
        print("sql执行成功,删除数据行数：",resultData)
    except:
        resultData=-1
        print("sql删除失败,请检查输入参数")
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为更新成功记录的数量

'''
下面是sqlite数据库连接查询数据、更新数据等方法********************************************************
'''
def sqlSelectDataSqlite(tableName, colName, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = sqlite3.connect(dataBasePara['NAME'])
    #conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    if whereConstraint!='':
        sqlStr = "select " + colNameString + " from " + tableName + " where " + whereConstraint
    else:
        sqlStr = "select " + colNameString + " from " + tableName
    #print(sqlStr)
    try:
        cur.execute(sqlStr)
        resultData = cur.fetchall() # 获取满足上述条件的记录
        print("sql执行成功，已获取选中数据！")
    except:
        print("查询数据未成功，请检查数据库参数！")
        resultData='fail'
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回取回的数据

def sqlUpdateDataLineSqlite(tableName, colName, updateData, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = sqlite3.connect(dataBasePara['NAME'])
    #conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    if(len(colName)==len(updateData)):
        colAndValue=''
        for i in range(len(colName)-1):
            colAndValue=colAndValue+colName[i]+"='"+updateData[i]+"', "
        colAndValue=colAndValue+colName[len(colName)-1]+"='"+updateData[len(updateData)-1]+"' "
        sql = "update " + tableName + " set " + colAndValue + " where " + whereConstraint
        try:
            resultData=cur.execute(sql).rowcount
            print("sql执行成功,改变选中数据行数：",resultData)
        except:
            resultData=-1
            print("sql执行失败,请检查输入参数")
    else:
        print('字段数量和保持字符串的数量不想等，请检查！')
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为更新成功记录的数量

def sqlInsertDataLineSqlite(tableName, colName, insertData, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = sqlite3.connect(dataBasePara['NAME'])
    #pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    dataString=dataMatrxiToString(insertData)
    sqlStr = 'insert into ' + tableName + ' ('+colNameString+') values(' + dataString +');' # 注意SQL语句必须是str文本
    # print(sqlStr)
    try:
        resultData=cur.execute(sqlStr).rowcount
        print("sql执行成功,插入数据行数：",resultData)
    except:
        resultData=-1
        print("sql执行失败,请检查输入参数")
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为插入成功记录的数量

def sqlInsertDataMultLineSqlite(tableName, colName, insertData, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = sqlite3.connect(dataBasePara['NAME'])
    #conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    colNameString=colNameMatrxiToString(colName)
    resultData=0
    for i in range(len(insertData)):
        dataString=dataMatrxiToString(insertData[i])
        sqlStr = 'insert into ' + tableName + ' ('+colNameString+') values(' + dataString +')' # 注意SQL语句必须是str文本
        try:
            tempData=cur.execute(sqlStr)
            resultData=resultData+tempData.rowcount
        except:
            resultData=-1
    # 下三行是关闭数据库连接
    if resultData==-1:
        print("sql执行失败,请检查输入参数")
    else:
        print("sql插入成功,插入数据行数：",resultData)
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为插入成功记录的数量

def sqlDeleteDataSqlite(tableName, whereConstraint, dataBasePara):#如果是mysql数据库，使用mysql数据库的连接方式
    if dataBasePara['CHARSET']=='':#默认字节集设置为'UTF8'
        dataBasePara['CHARSET']='UTF8'
    conn = sqlite3.connect(dataBasePara['NAME'])
    #conn = pymysql.connect(host=dataBasePara['HOST'], port=dataBasePara['PORT'], user=dataBasePara['USER'], passwd=dataBasePara['PASSWORD'], db=dataBasePara['NAME'], charset=dataBasePara['CHARSET'])
    cur = conn.cursor()
    sql = "delete from " + tableName + " where " + whereConstraint
    try:
        resultData=cur.execute(sql).rowcount
        print("sql执行成功,删除数据行数：",resultData)
    except:
        resultData=-1
        print("sql删除失败,请检查输入参数")
    # 下三行是关闭数据库连接
    cur.close()
    conn.commit()
    conn.close()
    return resultData # 返回值为更新成功记录的数量